﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Common
{
    public class CommonDO
    {
        public String getTable(SqlConnection conn, ref DataTable tb, String cmd)
        {
            string ret = CommonHeader.NO_ERROR;
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd, conn);
                da.Fill(ds);
                tb = ds.Tables[0];
                ds.Dispose();
            }
            catch (SqlException sqlex)
            {
                return sqlex.Message;
            }
            return ret;
        }

        public String InsertUpdateRecord(SqlConnection conn, string cmd)
        {
            string ret = CommonHeader.NO_ERROR;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand sqlCmd = new SqlCommand(cmd, conn);
                sqlCmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException sqlex)
            {
                return sqlex.Message;
            }            
            return ret;
        }
        public String UpdateRecord(SqlConnection conn, String cmd )
        {
            string ret = CommonHeader.NO_ERROR;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand sqlCmd = new SqlCommand(cmd, conn);
                sqlCmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException sqlex)
            {
                return sqlex.Message;
            }            
            return ret;            
        }

        public String UpdateRecordByID( SqlConnection conn, string storeName , string tableName, 
                                        string colUpdate,   string sUpdateValue,
                                        string colID,       string idValue )
        {
            try
            {
                string cmd = storeName;
                				
				cmd = string.Concat( cmd, " '" , tableName	 , "'" );
				cmd = string.Concat( cmd, ", '", colUpdate	 , "'" );
				cmd = string.Concat( cmd, ", N'", sUpdateValue , "'" );
				cmd = string.Concat( cmd, ", '", colID		 , "'" );
				cmd = string.Concat( cmd, ", N'", idValue	 , "'" );

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
				SqlCommand sqlCmd = new SqlCommand( cmd, conn );
				
                sqlCmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException sqlex)
            {
                return sqlex.Message;
            }
            return CommonHeader.NO_ERROR;
        }

        public String InsertRecordGetID(SqlConnection conn, 
                                        ref String sID ,
                                        String cmd)
        {
            string ret = CommonHeader.NO_ERROR;
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd, conn);
                da.Fill(ds);
                sID = ds.Tables[0].Rows[0][0].ToString();
                ds.Dispose();
            }
            catch (SqlException sqlex)
            {
                return sqlex.Message;
            }
            return ret;
        }

        public String DelereRecord(SqlConnection conn, string cmd)
        {
            string ret = CommonHeader.NO_ERROR;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand sqlCmd = new SqlCommand(cmd, conn);
                sqlCmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException sqlex)
            {
                return sqlex.Message;
            }
            return ret;              
        }

		public string DelereRecord(		SqlConnection conn, 
										string sTableName,
										string sColName,
										string sValue)
		{
			string ret = CommonHeader.NO_ERROR;
			
			string cmd = SqlHeader.SP_DEL_RECORD;
			
			cmd = string.Concat( cmd, " '" ,sTableName ,"'");
			cmd = string.Concat(cmd, ", '", sColName, "'");
			cmd = string.Concat(cmd, ", N'", sValue, "'");
			
			try
			{
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				SqlCommand sqlCmd = new SqlCommand(cmd, conn);
				sqlCmd.ExecuteNonQuery();
				conn.Close();
			}
			catch (SqlException sqlex)
			{
				return sqlex.Message;
			}
			return ret;
		}     

    }
}

